
package dbmanager;

// This class connects any application to database through getConnect() method as well performs all database 
//operations.
//The class is created on 24-10-2006.
// Last modified 27-11-2006,13:30

import java.sql.*;
import java.util.*;

import utility.CapitalChar;
import utility.Input;

// This class connects any application to database through getConnect() method as well performs all database 
//operations.
//The class is created on 24-10-2006.
// Last modified 27-11-2006,13:30

public class DBManager
{
	
    private Connection con;
    private Statement stmt;
    private ResultSet rs;
    private Savepoint savePoint;
	private String _driver="org.postgresql.Driver";
	private String _dsn = "//192.168.1.130/Wikiretail";
	private String _prefix = "jdbc:postgresql:";  //Driver for Linux
	   private String sqlUserName = "postgres";
       private String sqlPassword = "postgres";

    public DBManager()
    {
    	//Column.init();
    	
    	Input.init();
    	_prefix = Input.DB_PREFIX;
    	_driver = Input.DB_DRIVER;
    	
    	//MAKING CAPS 
      	int index = _driver.lastIndexOf('.');
    	String className = _driver.substring(index+1);
    	className = CapitalChar.makeFirstCharCapital(className);
    	_driver = _driver.substring(0, index+1)+className;
    	
    	_dsn = "//"+Input.DB_HOST+"/"+Input.DB_NAME;
    	sqlUserName = Input.DB_USERNAME;
    	sqlPassword = Input.DB_PASSWORD;
     }
    
    public DBManager(String driver,String  prefix,String dsn,String username, String password)
    {
    	_prefix = prefix;
    	
    	// MAKING CLASSNAME CAPITAL
    	int index = driver.lastIndexOf('.');
    	String className = driver.substring(index+1);
    	className = CapitalChar.makeFirstCharCapital(className);
    	driver = driver.substring(0, index+1)+className;
    	
    	_driver = driver;
    	_dsn = dsn;
    	sqlUserName = username;
    	sqlPassword = password;
    }
    
    public DBManager(String driver,String dsn)
    {
    	_driver = driver;
        _dsn = dsn;
    
    }
    
    public DBManager(String dsn)
    {
    	_dsn = dsn;
    
    }
    public void getConnect()
    {
       try
       {
    	   Class.forName(_driver);
    	   con=DriverManager.getConnection(_prefix+_dsn,sqlUserName,sqlPassword);
       }catch(Exception e)
       {
    	   System.out.println("Error in getConnect() method");
    	   e.printStackTrace();
       }    
    }
    
    
	public synchronized ResultSet getSelect(String table,Enumeration<Object> fields,Enumeration<Object> condition)
     {
    	try
        {
        	//Field List to be created
        	StringBuffer sbr = new StringBuffer();
        	Enumeration<Object> enum1 = fields;
        	Enumeration<Object> enum2 = condition;
        	while(enum1.hasMoreElements())
        	{
        		sbr.append(fields.nextElement());
        		sbr.append(",");
        	}
        	StringBuffer fieldList = new StringBuffer();
        	fieldList.append(sbr.subSequence(0, sbr.length()-1));
        	System.out.println("The fields are :"+fieldList);
        	
        	//Condition List is to be created
        	sbr = new StringBuffer();
        	while(enum2.hasMoreElements())
        	{
        		sbr.append(condition.nextElement());
        		sbr.append("AND");
        	}
        	StringBuffer conditionList = new StringBuffer();
        	conditionList.append(sbr.subSequence(0, sbr.length()-3));
        	System.out.println("The conditions are :"+conditionList);
        	
        	String _query = "SELECT "+fieldList+" FROM "+table+" WHERE "+conditionList;
        	System.out.println("The query is "+_query);
        	stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

           	rs=stmt.executeQuery(_query);
           	
        }catch(Exception e)
        {
        	System.out.println("Error in getSelect() method");
        	e.printStackTrace();
        }
       return rs;
     }
	public synchronized ResultSet getSelect(String table,Enumeration<Object> fields,Hashtable<Object,Object> condition)
    {
   	    try
       {
       	//Field List to be created
       	StringBuffer sbr = new StringBuffer();
       	Enumeration<Object> enum1 = fields;
       	Enumeration<Object> enum2 = condition.keys();
       	while(enum1.hasMoreElements())
       	{
       		sbr.append(fields.nextElement());
       		sbr.append(",");
       	}
       	StringBuffer fieldList = new StringBuffer();
       	fieldList.append(sbr.subSequence(0, sbr.length()-1));
       	System.out.println("The fields are :"+fieldList);
       	
       	//Condition List is to be created
       	sbr = new StringBuffer();
       	while(enum2.hasMoreElements())
       	{
       		String value= enum2.nextElement().toString(); 
       		sbr.append(value);
       		sbr.append("="+condition.get(value));
       		sbr.append("AND");
       	}
       	StringBuffer conditionList = new StringBuffer();
       	conditionList.append(sbr.subSequence(0, sbr.length()-3));
       	System.out.println("The conditions are :"+conditionList);
       	
       	String _query = "SELECT "+fieldList+" FROM "+table+" WHERE "+conditionList;
       	System.out.println("The query is "+_query);
       	stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

          	rs=stmt.executeQuery(_query);
          	
       }catch(Exception e)
       {
       	System.out.println("Error in getSelect() method");
       	e.printStackTrace();
       }       
       return rs;
    }
     public synchronized ResultSet getSelect(String query)
     {
        try
        {	
        	System.out.println("The query is "+query);
        	stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
           	rs=stmt.executeQuery(query);
        }
        catch(Exception e)
        {
        	System.out.println("Error in getSelect() method");
        	e.printStackTrace();
        }        
       return rs;
     }
     
     public synchronized Hashtable<Object,Object> getSelect(String query,int x)
     {
    	 Hashtable<Object,Object> temp = new Hashtable<Object,Object>();
        try
        {
        	System.out.println("The query is "+query);
        	stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
           	rs=stmt.executeQuery(query);
           	rs.next();
           	int i=1;
        	while(i!=rs.getMetaData().getColumnCount()+1)
           	{
           		String col = rs.getMetaData().getColumnName(i).toString();
           		String value = rs.getString(col);
           		//System.out.println("Col : "+col+"//tValue : "+value);
           		temp.put(col, value);
           		i++;
           	}
        	stmt.close();
        	rs.close();
        }
        catch(Exception e)
        {
        	System.out.println("Error in getSelect() method");
        	e.printStackTrace();
        }
       return temp;
     }
     
     
     public synchronized Vector<Object> getSelect(String query,char x)
     {
    	 Vector<Object> temp = new Vector<Object>();
        try
        {
        	System.out.println("The query is "+query);
        	stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
           	rs=stmt.executeQuery(query);
           	rs.next();
           	int i=1;
        	while(i!=rs.getMetaData().getColumnCount()+1)
           	{
           		String col = rs.getMetaData().getColumnName(i).toString();
           		//String value = rs.getString(col);
           		//System.out.println("Col : "+col+"//tValue : "+value);
           		temp.add(col.toLowerCase());
           		i++;
           	}
        }
        catch(Exception e)
        {
        	System.out.println("Error in getSelect() method");
        	e.printStackTrace();
        }        
       return temp;
     }
     
     /**
      * @see getSelectVector method which is written to select the records from the database according to the the condition 
      * given in hashtable. 
      * @param hash  is a hashtable of conditions to select the records.
      * @param tablename 
      * @return vector of selected records from database according to the condition. 
      */
     public synchronized Vector<Object>  getSelectVector(Hashtable<Object, Object> hash,String tablename) 
     {
    	 Vector<Object> temp = new Vector<Object>();
         StringBuffer query = new StringBuffer("select * from "+tablename+" where ");
	   	 Enumeration<Object> hashen = hash.keys();
	   	 while(hashen.hasMoreElements())
	   	 { 
	   		Object t = hashen.nextElement();
	   		String value= hash.get(t).toString();
	   		if(value.equalsIgnoreCase("null"))
	   			query = query.append(t.toString() + " is " + value+ " and ");
	   		else	
	   			query = query.append(t.toString() + " = " + "'" + value+  "'" + " and ");
	   	 }
	   	 query.delete(query.length()-5, query.length());
	   	 ResultSet rs =  getSelect(query.toString());
	   	 
	   	 try
	   	 {
	   		ResultSetMetaData metaData = rs.getMetaData();
			while(rs.next())
			{
				Hashtable< Object, Object> tempHash = new Hashtable<Object, Object>();
			 	int i=1;
				while(i!=metaData.getColumnCount()+1)
			   	{
					String col=null;
			   		try 
			   		{
						col = metaData.getColumnName(i).toString();
						String value = rs.getString(col);
						//System.out.println("Col : "+col+"//tValue : "+value);
						tempHash.put(col, value);
					} catch (NullPointerException e) {
						tempHash.put(col, "null");
					}
			   		i++;
			   	}
				temp.add(tempHash);
			 }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return temp;
     }
     
     
    public synchronized int getDelete(String table,Enumeration<Object> condition)
      {
    	 int i=0;
    	  try
    	  {    		
    		  	//Condition List is to be created
    		    StringBuffer sbr = new StringBuffer();
          		Enumeration<Object> enum1 = condition;
          		while(enum1.hasMoreElements())
            	{
            		sbr.append(condition.nextElement());
            		sbr.append("AND");
            	}
          		StringBuffer conditionList = new StringBuffer();
            	conditionList.append(sbr.subSequence(0, sbr.length()-3));
            	System.out.println("The conditions are :"+conditionList);
            	String _query = "DELETE FROM "+table+" WHERE "+conditionList;
          		System.out.println("The query is :"+_query);
          		stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
          		int j=stmt.executeUpdate(_query);
          		if(j>0)
          		{
    			  i=1;    			
          		}
    	  }
    	  catch(Exception e)
    	  {    		
    		  e.printStackTrace();
    	  }
    	  return i;
      }
    
    
    public synchronized int getDelete(String table,Hashtable<Object,Object> h)
    {
    int i=0;
  	  try
  	  {
  		  	//Condition List is to be created  		
  		    StringBuffer sbr = new StringBuffer();
  		    Enumeration<Object> keys = h.keys();
        		while(keys.hasMoreElements())
        		{
        			String key =keys.nextElement().toString();
      			  	String value = h.get(key).toString();
        			sbr.append(" "+key);
        			sbr.append("= '");
        			sbr.append(value+"' ");
        			sbr.append("AND");
        		}
        		StringBuffer conditionList = new StringBuffer();
        		conditionList.append(sbr.subSequence(0, sbr.length()-3));
        		System.out.println("The conditions are :"+conditionList);
        		String _query = "DELETE FROM "+table+" WHERE "+conditionList;
        		System.out.println("The query is :"+_query);
        		stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        		int j=stmt.executeUpdate(_query);
        		if(j>0)
        		{
        			i=1;        			
        		}
  	  	}
	  	 catch(Exception e)
	  	 {	  		
	  		  e.printStackTrace();
	  	 }
	  return i;
	 
    }
    
    
    
     public synchronized int getDelete(String query)
     {
    	int i=0;
   	  	try
   	  	{
   		  	System.out.println("The query is :"+query);
         	stmt=con.createStatement();
         	int j=stmt.executeUpdate(query);
         	if(j>0)
         	{
         		i=1;   			
         	}
   	  	}
   	  	catch(Exception e)
   	  	{   	  		
   	  		e.printStackTrace();
   	  	}
   	  	return i;
     }
     
     
      public synchronized int getInsert(String table,Hashtable<Object,Object> h)
      {
    	  int i=0;
    	  try
    	  {
    		  
    		  StringBuffer sb1 = new StringBuffer();
    		  StringBuffer sb2 = new StringBuffer();
    		  sb1.append("INSERT INTO "+table+"(");
    		  sb2.append(" VALUES(");
    		  Enumeration<Object> keys = h.keys();
    		  while(keys.hasMoreElements())
    		  {
    			  String key =keys.nextElement().toString();
    			  String value = h.get(key).toString();
    			  sb1.append(key+",");
    			  if(value.length()==0)
    				  sb2.append(""+null+",");
    			  else if(value.equalsIgnoreCase("null"))
    				  sb2.append(""+value+",");
    			  else
    				  sb2.append("'"+value+"',");
    		  }
    		  StringBuffer query = new StringBuffer(sb1.subSequence(0, sb1.length()-1)+")");
    		  query.append(sb2.subSequence(0, sb2.length()-1)+")");
    		  System.out.println("The query is :"+query.toString());
    		  stmt=con.createStatement();
    		  int j=stmt.executeUpdate(query.toString());
    		  System.out.println(j);
    		  if(j>0)
    		  {
    			 System.out.println("Data inserted successfully");
    			 i=1;    			
    		  }
    	  }
    	  catch(Exception e)
    	  {    		
    		  e.printStackTrace();
    	  }
    	  return i;
      }
      
      
    
      public synchronized int getInsert(String query)
      {
    	  int i=0;
    	  try{
    		  
    		  	System.out.println("The query is :"+query);
    		  	stmt=con.createStatement();
    		  	int j=stmt.executeUpdate(query);
    		  	if(j>0)
    		  	{
    			  i=1;    			 
    		  	}
    	  }
    	  catch(Exception e)
    	  {    		  
    		  e.printStackTrace();
    	  }
    	  return i;
      }
      
      
      
      public synchronized int getUpdate(String query)
      {
    	  int i = 0;
    	  try{
    		  
    		  String _query = query;
    		  System.out.println("The query is :"+query);
    		  stmt=con.createStatement();
    		  int j=stmt.executeUpdate(_query);
    		  if(j>0)
    		  {
    			  i=1;    			 
    		  }
    	  }
    	  catch(Exception e)
    	  {    		  
    		  e.printStackTrace();
    	  }
    	  return i;
      }
      
      
      
      public synchronized int getUpdate(String table,Hashtable<Object,Object> h, Hashtable<Object,Object> condition)
      {
    	  int i = 0;
    	  try
    	  {
    		 
    		  StringBuffer sb = new StringBuffer();
    		  sb.append("UPDATE "+table+" SET ");
    		  Enumeration<Object> keys = h.keys();
    		  while(keys.hasMoreElements())
    		  {
    			  String key =(String)keys.nextElement();
    			  String value = (String)h.get(key);
    			  if(value.equalsIgnoreCase("null"))
    				  sb.append(" "+key+"="+value+",");
    			  else
    				  sb.append(" "+key+"='"+value+"',");
    			  
    		  }
    		  StringBuffer query = new StringBuffer(sb.subSequence(0, sb.length()-1));
    		  query.append(" where ");
    		  Enumeration<Object> conditions = condition.keys();
    		  sb = new StringBuffer();
    		  while(conditions.hasMoreElements())
    		  {
    			  String key = (String)conditions.nextElement();
    			  String value = String.valueOf(condition.get(key));
    			  
    			  if(value.equalsIgnoreCase("null"))
    				  sb.append(" "+key+" is "+value+" and");
    			  else
    				  sb.append(" "+key+"='"+value+"' and");
    		  }
    		  query.append(sb.subSequence(0,sb.length()-3));
    		  System.out.println("The query is :"+query);
    		  stmt=con.createStatement();
    		  int j=stmt.executeUpdate(query.toString());
    		  if(j>0)
    		  {
    			  i=1;    			
    		  }
    	  }
    	  catch(Exception e)
    	  {    		  
    		  e.printStackTrace();
    	  }
    	  return i;
      }
      
      
      
      public synchronized void closeConnection()
      {
    	  try
    	  {
    		  if(con.isClosed()==false)
    		  {
    			  con.close(); 	
    		  }
    	  }
    	  catch(Exception e)
    	 {
    	 	e.printStackTrace();
    	 }
      }
      
      
      
      public synchronized ResultSet getselect(Hashtable<Object, Object> hash,String tablename) 
      {
    	 StringBuffer query = new StringBuffer("select * from "+tablename+" where ");
    	 Enumeration<Object> hashen = hash.keys();
    	 while(hashen.hasMoreElements())
    	 { 
    		Object t = hashen.nextElement();
    		String value = hash.get(t).toString();
    		if(value.equalsIgnoreCase("null"))
                query = query.append(t.toString() + " is " + value + " and ");
    		else
                query = query.append(t.toString() + " = " + "'" + hash.get(t).toString() +  "'" + " and ");
    		
    	 }
    	 query.delete(query.length()-5, query.length());
    	 ResultSet rs =  getSelect(query.toString());    	 
    	return rs;
      }
      
      
      
      public synchronized Hashtable<Object,Object> getSelectCheck(String query)
      {
    	 Hashtable<Object,Object> temp = new Hashtable<Object,Object>();
     	 try
     	 {
     		 System.out.println("The query is "+query);
     		 stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
     		 rs=stmt.executeQuery(query);
     		 int rowCount = 0;
     		 //  rs = getselect(h, table);
     		 while(rs.next())
     		 {
     			 rowCount++;
     			 if(rowCount>=2)
     				 break;
     		 }
    		  
     		 if(rowCount<2)
     		 {
     			 rs=stmt.executeQuery(query);
     			 rs.next();
     			 int i=1;
     			 
     			 while(i!=rs.getMetaData().getColumnCount()+1)
     			 {
     				 try 
     				 {
						String col = rs.getMetaData().getColumnName(i).toString();
						 String value = rs.getString(col);
						 //System.out.println("Col : "+col+"\tValue : "+value);
						 temp.put(col, value);
						 i++;
					} catch (Exception e) {
						i++;
					}
     			 }   		
     		 }
     		 else
     		 {
     			 temp.put("Error", "Returns More Than One Query");
     			 return temp;
     		 }
     	 } 
     	 catch(Exception e)
     	 {
     		 System.out.println("Error in getSelect() method");
     		 e.printStackTrace();
     	 }
     	 finally
     	 {
     		 try 
     		 {
				rs.close();
			}
     		 catch (SQLException e) 
     		 {
			  }
     	 }
     	 return temp;
    }
      
      
      public synchronized Hashtable<Object,Object> getSelectCheck( Hashtable<Object,Object> queryHash, String tableName)
      {
    	 Hashtable<Object,Object> temp = new Hashtable<Object,Object>();
     	 try
     	 {
     		 stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
     		 rs= getselect(queryHash,tableName);
     		 int rowCount = 0;
     		 //  rs = getselect(h, table);
     		 while(rs.next())
     		 {
     			 rowCount++;
     			 if(rowCount>=2)
     				 break;
     		 }
    		  
     		 if(rowCount<2)
     		 {
     			 rs.beforeFirst();
     			 rs.next();
     			 int i=1;
     			 
     			 while(i!=rs.getMetaData().getColumnCount()+1)
     			 {
     				 try {
						String col = rs.getMetaData().getColumnName(i).toString();
						 String value = rs.getString(col);
							 temp.put(col, value);
						 i++;
					}
     				 catch (Exception e) 
					 {
						 i++;		
					 }
     			 }   		
     		 }
     		 else
     		 {
     			 temp.put("Error", "Returns More Than One Query");
     			 return temp;
     		 }
     	 } 
     	 catch(Exception e)
     	 {
     		 System.out.println("Error in getSelect() method");
     		 e.printStackTrace();
     	 }
     	 finally
     	 {
     	   try
     	   {
			rs.close();
     	   } 
     	   catch (SQLException e) {}	 
     	 }     	 
     	 return temp;
    }
      
      
      /*
       * added to create table for tablization
       */
      public synchronized void createTable(String tableName, Vector<Object> columnName)
      {  try
    	  {
    		  StringBuffer query = new StringBuffer();
    		  
    		  query.append("CREATE TABLE "+tableName+" ("); 
    		  
    		  for(int i=0;i<columnName.size();i++)
    			  query.append("\""+columnName.get(i).toString().toLowerCase()+"\""+" text,");
    		  
    		  query = new StringBuffer(query.subSequence(0,query.length()-1));
    		  query.append(")");
    		  
    		  System.out.println("Query is "+query.toString());
    		  getExecute(query.toString());
    		  System.out.println("Table Created Successfully");
    		  		
    	  }
    	  catch (Exception e) {
			e.printStackTrace();
		}    	
      }
      
      
      public void getExecute(String query)
      { 
    	  try
          {	
          	System.out.println("The query is "+query);
          	stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stmt.execute(query);
          
            System.out.println("Query Executed Successfully");
          }
          catch(Exception e)
          {
          	System.out.println("Error in getExecute() method");
          	e.printStackTrace();
          }      
      }
      
    
      
      public void setBeforeCommit()
      {
		try 
		{			

			//con.setAutoCommit(false);
			//savePoint = con.setSavepoint();				

		//	con.setAutoCommit(false);
		//	savePoint = con.setSavepoint();				

		//	System.out.println("savepoint "+savePoint);
			
		} 
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
      }
     
      public void setAfterCommit(boolean flag)
	  {    	  
			try 
			{
				if(flag == true)
				{	

				//	con.commit();					
					//con.setAutoCommit(true);		
					System.out.println("         Committed             ");



					//con.commit();					
					//con.setAutoCommit(true);					


				//	con.commit();					
				//	con.setAutoCommit(true);					


				}
				else
				{
					//con.rollback(savePoint);					

		//			con.rollback(savePoint);					
				}
				
			} 
			catch (Exception e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
			
	   }  
      public static void main(String[] args) {
		DBManager db = new DBManager();
		db.getConnect();
	}
     
    
 }